package com.F06.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F06.actionForm.F060101ActionForm;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.util.CommonTools;

/**
 * @wangk071226
 * @综合统计下载
 * @功能：生成数据倒入EXCEL
 */
public class F060102DAO extends DbOperationExt {

	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F060102DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F060102DAO() {
		super();
	}

	public F060102DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @第一页数据
	 */
	public List sheet0(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		msql.append(" select adyh.FUN_CPXH(a.manufacturing_order) as '型号',																																											");						
		msql.append("        isnull(sum(convert (float, adyh.FUN_GETProductCost(a.manufacturing_order))),0) as                                                              ");
		msql.append("         '材料成本',                                                                                                                         ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_GETMANUFACTURECOST(a.manufacturing_order))),0)                                                             ");
		msql.append("         as '人工',                                                                                                                          ");
		msql.append("        isnull(sum(convert (float, a.transport)),0) as '运费',                                                                                         ");
		msql.append("        isnull(sum(convert (float, a.commission)),0) as '佣金',                                                                                        ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '1'))),0)                                                             ");
		msql.append("         as '计划订单数量1',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '1'))),0)                                                             ");
		msql.append("         as '实际订单数量1',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价1',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'1')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税1',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税1',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利1',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '2'))),0)                                                             ");
		msql.append("         as '计划订单数量2',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '2'))),0)                                                             ");
		msql.append("         as '实际订单数量2',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价2',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'2')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税2',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税2',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利2',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '3'))),0)                                                             ");
		msql.append("         as '计划订单数量3',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '3'))),0)                                                             ");
		msql.append("         as '实际订单数量3',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价3',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'3')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税3',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税3',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利3',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '4'))),0)                                                             ");
		msql.append("         as '计划订单数量4',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '4'))),0)                                                             ");
		msql.append("         as '实际订单数量4',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价4',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'4')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税4',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税4',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利4',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '5'))),0)                                                             ");
		msql.append("         as '计划订单数量5',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '5'))),0)                                                             ");
		msql.append("         as '实际订单数量5',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价5',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'5')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税5',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税5',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利5',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '6'))),0)                                                             ");
		msql.append("         as '计划订单数量6',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '6'))),0)                                                             ");
		msql.append("         as '实际订单数量6',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价6',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'6')/(1+(convert(float,a.VAT*0.01))))))/10000,0)  as  '定货值不含税6',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税6',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利6',                                                                                                 ");
		msql.append("                                                                                                                                             ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '7'))),0)                                                             ");
		msql.append("         as '计划订单数量7',                                                                                                                 ");
		msql.append("        isnull(sum(convert (float, adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '7'))),0)                                                             ");
		msql.append("         as '实际订单数量7',                                                                                                                 ");
		msql.append("        isnull(convert (float, ''),0) as '单价7',                                                                                                                       ");
		msql.append("        isnull(sum(convert (float,(adyh.FUN_REPORT_TOTAL_PRICE(a.manufacturing_order,'7')/(1+(convert(float,a.VAT*0.01)))))),0)  as  '定货值不含税7',  ");
		msql.append("        isnull(sum(convert (float, '')),0) as '总成本不含税7',                                                                                         ");
		msql.append("        isnull(sum(convert (float, '')),0) as '毛利7'                                                                                                  ");
		msql.append(" from c_orders a   ");
	//	msql.append(" where a.signaure_date BETWEEN adyh.FUN_NC_TIME('"+time+"') and ");
    //    msql.append(" adyh.FUN_NM_TIME('"+time+"') ");
        msql.append(" group by adyh.FUN_CPXH(a.manufacturing_order) ");
        
		try {
			log.info("毛利下载 sheet0 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("型号", CommonTools.null2String(rest.getString("型号")));
				map.put("材料成本", CommonTools.null2String(rest.getString("材料成本")));
				map.put("人工", CommonTools.null2String(rest.getString("人工")));
				map.put("运费", CommonTools.null2String(rest.getString("运费")));
				map.put("佣金", CommonTools.null2String(rest.getString("佣金")));
				
				map.put("计划订单数量1", CommonTools.null2String(rest.getString("计划订单数量1")));
				map.put("实际订单数量1", CommonTools.null2String(rest.getString("实际订单数量1")));
				map.put("单价1", CommonTools.null2String(rest.getString("单价1")));
				map.put("定货值不含税1", CommonTools.null2String(rest.getString("定货值不含税1")));
				map.put("总成本不含税1", CommonTools.null2String(rest.getString("总成本不含税1")));
				map.put("毛利1", CommonTools.null2String(rest.getString("毛利1")));
				
				map.put("计划订单数量2", CommonTools.null2String(rest.getString("计划订单数量1")));
				map.put("实际订单数量2", CommonTools.null2String(rest.getString("实际订单数量1")));
				map.put("单价2", CommonTools.null2String(rest.getString("单价2")));
				map.put("定货值不含税2", CommonTools.null2String(rest.getString("定货值不含税2")));
				map.put("总成本不含税2", CommonTools.null2String(rest.getString("总成本不含税2")));
				map.put("毛利2", CommonTools.null2String(rest.getString("毛利2")));
				
				map.put("计划订单数量3", CommonTools.null2String(rest.getString("计划订单数量3")));
				map.put("实际订单数量3", CommonTools.null2String(rest.getString("实际订单数量3")));
				map.put("单价3", CommonTools.null2String(rest.getString("单价3")));
				map.put("定货值不含税3", CommonTools.null2String(rest.getString("定货值不含税3")));
				map.put("总成本不含税3", CommonTools.null2String(rest.getString("总成本不含税3")));
				map.put("毛利3", CommonTools.null2String(rest.getString("毛利3")));
				
				map.put("计划订单数量4", CommonTools.null2String(rest.getString("计划订单数量4")));
				map.put("实际订单数量4", CommonTools.null2String(rest.getString("实际订单数量4")));
				map.put("单价4", CommonTools.null2String(rest.getString("单价4")));
				map.put("定货值不含税4", CommonTools.null2String(rest.getString("定货值不含税4")));
				map.put("总成本不含税4", CommonTools.null2String(rest.getString("总成本不含税4")));
				map.put("毛利4", CommonTools.null2String(rest.getString("毛利4")));
				
				map.put("计划订单数量5", CommonTools.null2String(rest.getString("计划订单数量5")));
				map.put("实际订单数量5", CommonTools.null2String(rest.getString("实际订单数量5")));
				map.put("单价5", CommonTools.null2String(rest.getString("单价5")));
				map.put("定货值不含税5", CommonTools.null2String(rest.getString("定货值不含税5")));
				map.put("总成本不含税5", CommonTools.null2String(rest.getString("总成本不含税5")));
				map.put("毛利5", CommonTools.null2String(rest.getString("毛利5")));
				
				map.put("计划订单数量6", CommonTools.null2String(rest.getString("计划订单数量6")));
				map.put("实际订单数量6", CommonTools.null2String(rest.getString("实际订单数量6")));
				map.put("单价6", CommonTools.null2String(rest.getString("单价6")));
				map.put("定货值不含税6", CommonTools.null2String(rest.getString("定货值不含税6")));
				map.put("总成本不含税6", CommonTools.null2String(rest.getString("总成本不含税6")));
				map.put("毛利6", CommonTools.null2String(rest.getString("毛利6")));
				
				map.put("计划订单数量7", CommonTools.null2String(rest.getString("计划订单数量7")));
				map.put("实际订单数量7", CommonTools.null2String(rest.getString("实际订单数量7")));
				map.put("单价7", CommonTools.null2String(rest.getString("单价7")));
				map.put("定货值不含税7", CommonTools.null2String(rest.getString("定货值不含税7")));
				map.put("总成本不含税7", CommonTools.null2String(rest.getString("总成本不含税7")));
				map.put("毛利7", CommonTools.null2String(rest.getString("毛利7")));

				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	/**
	 * @第一页数据
	 */
	public List sheet0Sum(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		msql.append(" select adyh.FUN_CPXH(a.manufacturing_order) as '型号',");
		msql.append("        sum(convert(float,adyh.FUN_GETProductCost(a.manufacturing_order))) as '材料成本',");
		msql.append("        sum(convert(float,adyh.FUN_GETMANUFACTURECOST(a.manufacturing_order))) as '人工',");
		msql.append("        sum(convert(float,a.transport)) as '运费',");
		msql.append(" 	     sum(convert(float,a.commission)) as '佣金',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '1'))) as '计划订单数量1',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '1'))) as '实际订单数量1',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '1'))/(1+a.VAT*0.01)) as '单价1',");
		msql.append("        sum(convert(float,'')) as '定货值不含税1',");
		msql.append("        sum(convert(float,''))  as '总成本不含税1',");
		msql.append("        sum(convert(float,'')) as '毛利1',     ");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '2'))) as '计划订单数量2',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '2'))) as '实际订单数量2',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '2'))/(1+a.VAT*0.01)) as '单价2',");
		msql.append("        sum(convert(float,'')) as '定货值不含税2',");
		msql.append("        sum(convert(float,''))  as '总成本不含税2',");
		msql.append("        sum(convert(float,'')) as '毛利2',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '3'))) as '计划订单数量3',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '3'))) as '实际订单数量3',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '3'))/(1+a.VAT*0.01)) as '单价3',");
		msql.append("        sum(convert(float,'')) as '定货值不含税3',");
		msql.append("        sum(convert(float,''))  as '总成本不含税3',");
		msql.append("        sum(convert(float,'')) as '毛利3',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '4'))) as '计划订单数量4',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '4'))) as '实际订单数量4',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '4'))/(1+a.VAT*0.01)) as '单价4',");
		msql.append("        sum(convert(float,'')) as '定货值不含税4',");
		msql.append("        sum(convert(float,''))  as '总成本不含税4',");
		msql.append("        sum(convert(float,'')) as '毛利4',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '5'))) as '计划订单数量5',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '5'))) as '实际订单数量5',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '5'))/(1+a.VAT*0.01)) as '单价5',");
		msql.append("        sum(convert(float,'')) as '定货值不含税5',");
		msql.append("        sum(convert(float,''))  as '总成本不含税5',");
		msql.append("        sum(convert(float,'')) as '毛利5',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '6'))) as '计划订单数量6',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '6'))) as '实际订单数量6',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '6'))/(1+a.VAT*0.01)) as '单价6',");
		msql.append("        sum(convert(float,'')) as '定货值不含税6',");
		msql.append("        sum(convert(float,''))  as '总成本不含税6',");
		msql.append("        sum(convert(float,'')) as '毛利6',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_JHDDSL(a.manufacturing_order, '7'))) as '计划订单数量7',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_SJDDSL(a.manufacturing_order, '7'))) as '实际订单数量7',");
		msql.append("        sum(convert(float,adyh.FUN_REPORT_UNIT_PRICE(a.manufacturing_order, '7'))/(1+a.VAT*0.01)) as '单价7',");
		msql.append("        sum(convert(float,'')) as '定货值不含税7',");
		msql.append("        sum(convert(float,''))  as '总成本不含税7',");
		msql.append("        sum(convert(float,'')) as '毛利7'");
		msql.append(" from c_orders a   ");
	//	msql.append(" where a.signaure_date BETWEEN adyh.FUN_NC_TIME('"+time+"') and ");
    //    msql.append(" adyh.FUN_NM_TIME('"+time+"') ");
        msql.append(" group by adyh.FUN_CPXH(a.manufacturing_order) ");
        
		try {
			log.info("毛利下载 sheet0 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("型号", CommonTools.null2String(rest.getString("型号")));
				map.put("材料成本", CommonTools.null2String(rest.getString("材料成本")));
				map.put("人工", CommonTools.null2String(rest.getString("人工")));
				map.put("运费", CommonTools.null2String(rest.getString("运费")));
				map.put("佣金", CommonTools.null2String(rest.getString("佣金")));
				
				map.put("计划订单数量1", CommonTools.null2String(rest.getString("计划订单数量1")));
				map.put("实际订单数量1", CommonTools.null2String(rest.getString("实际订单数量1")));
				map.put("单价1", CommonTools.null2String(rest.getString("单价1")));
				map.put("定货值不含税1", CommonTools.null2String(rest.getString("定货值不含税1")));
				map.put("总成本不含税1", CommonTools.null2String(rest.getString("总成本不含税1")));
				map.put("毛利1", CommonTools.null2String(rest.getString("毛利1")));
				
				map.put("计划订单数量2", CommonTools.null2String(rest.getString("计划订单数量1")));
				map.put("实际订单数量2", CommonTools.null2String(rest.getString("实际订单数量1")));
				map.put("单价2", CommonTools.null2String(rest.getString("单价2")));
				map.put("定货值不含税2", CommonTools.null2String(rest.getString("定货值不含税2")));
				map.put("总成本不含税2", CommonTools.null2String(rest.getString("总成本不含税2")));
				map.put("毛利2", CommonTools.null2String(rest.getString("毛利2")));
				
				map.put("计划订单数量3", CommonTools.null2String(rest.getString("计划订单数量3")));
				map.put("实际订单数量3", CommonTools.null2String(rest.getString("实际订单数量3")));
				map.put("单价3", CommonTools.null2String(rest.getString("单价3")));
				map.put("定货值不含税3", CommonTools.null2String(rest.getString("定货值不含税3")));
				map.put("总成本不含税3", CommonTools.null2String(rest.getString("总成本不含税3")));
				map.put("毛利3", CommonTools.null2String(rest.getString("毛利3")));
				
				map.put("计划订单数量4", CommonTools.null2String(rest.getString("计划订单数量4")));
				map.put("实际订单数量4", CommonTools.null2String(rest.getString("实际订单数量4")));
				map.put("单价4", CommonTools.null2String(rest.getString("单价4")));
				map.put("定货值不含税4", CommonTools.null2String(rest.getString("定货值不含税4")));
				map.put("总成本不含税4", CommonTools.null2String(rest.getString("总成本不含税4")));
				map.put("毛利4", CommonTools.null2String(rest.getString("毛利4")));
				
				map.put("计划订单数量5", CommonTools.null2String(rest.getString("计划订单数量5")));
				map.put("实际订单数量5", CommonTools.null2String(rest.getString("实际订单数量5")));
				map.put("单价5", CommonTools.null2String(rest.getString("单价5")));
				map.put("定货值不含税5", CommonTools.null2String(rest.getString("定货值不含税5")));
				map.put("总成本不含税5", CommonTools.null2String(rest.getString("总成本不含税5")));
				map.put("毛利5", CommonTools.null2String(rest.getString("毛利5")));
				
				map.put("计划订单数量6", CommonTools.null2String(rest.getString("计划订单数量6")));
				map.put("实际订单数量6", CommonTools.null2String(rest.getString("实际订单数量6")));
				map.put("单价6", CommonTools.null2String(rest.getString("单价6")));
				map.put("定货值不含税6", CommonTools.null2String(rest.getString("定货值不含税6")));
				map.put("总成本不含税6", CommonTools.null2String(rest.getString("总成本不含税6")));
				map.put("毛利6", CommonTools.null2String(rest.getString("毛利6")));
				
				map.put("计划订单数量7", CommonTools.null2String(rest.getString("计划订单数量7")));
				map.put("实际订单数量7", CommonTools.null2String(rest.getString("实际订单数量7")));
				map.put("单价7", CommonTools.null2String(rest.getString("单价7")));
				map.put("定货值不含税7", CommonTools.null2String(rest.getString("定货值不含税7")));
				map.put("总成本不含税7", CommonTools.null2String(rest.getString("总成本不含税7")));
				map.put("毛利7", CommonTools.null2String(rest.getString("毛利7")));

				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

}
